"""
Friendship相关处理逻辑
"""


def get_session_list(db, logger, data: dict):
    try:
        mac = data["mac_addr"]
        # 根据用户mac地址查询用户信息
        user_info = db.get_one("User", {"mac_addr": mac})
        if not user_info:
            return []

        sessions = []

        # 1) 单聊会话：找出与我相关的所有对端
        peer_sql = """
        SELECT DISTINCT peer_mac FROM (
            SELECT CASE WHEN source = ? THEN target ELSE source END AS peer_mac
            FROM Message
            WHERE source = ? OR target = ?
        )
        """
        peers = db.execute_query(peer_sql, (mac, mac, mac))

        for row in peers:
            peer_mac = row["peer_mac"]
            peer_user = db.get_one("User", {"mac_addr": peer_mac}) or {"mac_addr": peer_mac, "name": peer_mac}

            # 最近一条单聊消息
            last_msg = db.execute_query(
                """
                SELECT id, source, target, context, created_at AS last_time
                FROM Message
                WHERE (source = ? AND target = ?) OR (source = ? AND target = ?)
                ORDER BY last_time DESC
                LIMIT 1
                """,
                (mac, peer_mac, peer_mac, mac),
            )

            last_msg = last_msg[0] if last_msg else {}

            # 未读数（对端->我 且未读）
            unread = db.get_count("Message", {"source": peer_mac, "target": mac, "status": "unread"})

            # 查询是否好友关系
            friendship = db.get_one("Friendship", {"master_id": user_info.get("id"), "user_id": peer_user.get("id")})

            sessions.append(
                {
                    "session_id": f"user_{peer_user.get('id', peer_mac)}",
                    "category": "user",
                    "name": peer_user.get("name", peer_mac),
                    "mac_addr": peer_mac,
                    "status": peer_user.get("status", "offline"),
                    "last_message": last_msg,
                    "last_time": (last_msg["last_time"] if last_msg else 0),
                    "is_friend": bool(friendship),
                    "remark_name": friendship.get("remark_name") if friendship else None,
                    "unread": unread,
                }
            )

        # 2) 群聊会话：我所在的群
        teams = db.execute_query(
            """
            SELECT ut.*, t.*
            FROM User_Team ut
            JOIN Team t ON ut.team_id = t.id
            WHERE ut.user_id = ?
            """,
            (user_info["id"],),
        )

        for team in teams:
            last_team_msg = db.execute_query(
                """
                SELECT tm.*, tm.created_at AS last_time, u.name AS sender_name
                FROM Team_Message tm
                JOIN User u ON tm.user_id = u.id
                WHERE team_id = ?
                ORDER BY last_time DESC
                LIMIT 1
                """,
                (team["id"],),
            )

            last_team_msg = last_team_msg[0] if last_team_msg else {}

            team_msg_count = db.get_count("Team_Message", {"team_id": team["id"]})
            user_read_count = db.get_count("Team_Message_Read", {"team_id": team["id"], "user_id": user_info["id"]})
            unread = max(team_msg_count - user_read_count, 0)

            sessions.append(
                {
                    "session_id": f"team_{team['id']}",
                    "category": "team",
                    "name": team.get("name", f"team_{team['id']}"),
                    "team_id": team["id"],
                    "last_message": last_team_msg,
                    "last_time": (last_team_msg["last_time"] if last_team_msg else 0),
                    "unread": unread,
                }
            )

        sessions.sort(key=lambda x: (x["unread"], x["category"], x["name"]), reverse=True)
        return sessions
    except Exception as e:
        raise e
